package com.hw.poi;

import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellRangeAddress;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * @author hewei8<br>
 * Date 2019年2月15日<br>
 * <p>基于POI3.9</p>
 */
public class PoiExport {

	public void toDo(String excType) {

		ArrayList<User> userList = new ArrayList<User>();
		Map<String, String> head = new LinkedHashMap<String,String>();
		
		head.put("姓名", "文本类型，必填");
		head.put("性别", "文本类型，填男或女，必填");
		head.put("年龄", "文本类型，必填");

		User user = new User("何伟", "男", 18);
		userList.add(user);
		User user1 = new User("马青青", "女", 16);
		userList.add(user1);
		User user2 = new User("马龙", "男", 50);
		userList.add(user2);
		
		String description="备注：";

		int headRow=4;
		
		String path="d:/";
		String fileName="用户信息";
		
		if (excType.equals("xls")) {
			generateXls(userList, head, description, headRow,path,fileName);
		}else if (excType.equals("xlsx")) {
			generateXlsx(userList, head, description, headRow,path,fileName);
		}
	}

	/**
	 * @param userList
	 * @param head
	 * @param description
	 * @param headRow<br>
	 * <p>导出xls</p>
	 */
	public void generateXls(List<User> userList, Map<String,String> head,String description,int headRow,String path,String fileName) {

		// 1、创建工作簿
//		// excel的文档对象
		HSSFWorkbook wb = new HSSFWorkbook();
		// cell样式
		HSSFCellStyle style = wb.createCellStyle();

		HSSFFont font = wb.createFont();
		font.setFontName("仿宋_GB2312");
//		font.setFontName("黑体");
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 粗体显示
		font.setFontHeightInPoints((short) 12);

		style.setFont(font);// 选择需要用到的字体格式

		// 设置这些样式
		// 水平居中
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		// 垂直居中
		style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		// 背景色
		
		style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
		style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		style.setFillBackgroundColor(HSSFColor.SKY_BLUE.index);

		// 设置边框
		style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		style.setBorderRight(HSSFCellStyle.BORDER_THIN);
		style.setBorderTop(HSSFCellStyle.BORDER_THIN);

		// 2、创建工作页
		HSSFSheet sheet = wb.createSheet("用户信息");
		for (int i = 0; i < head.size(); i++) {
//			设置列宽
			sheet.setColumnWidth(i, 7000);
		}


		// 3.创建行
//		表头
		HSSFRow row = sheet.createRow(0);
//		表头说明
		HSSFRow row1 = sheet.createRow(1);
		
		
		int column=0;
		for(String title:head.keySet()) {
			
			HSSFCell cell = row.createCell(column);
			cell.setCellValue(title);
			cell.setCellStyle(style);
			
			HSSFCell cell1 = row1.createCell(column);
			cell1.setCellValue(head.get(title));
			cell1.setCellStyle(style);
			
			column++;
		}

//		文档备注行
		HSSFRow row3=sheet.createRow(2);
//		row3.setRowStyle(style);
		HSSFRow row4=sheet.createRow(3);
//		row4.setRowStyle(style);
		HSSFCell cell3=row3.createCell(0);
		cell3.setCellValue(description);
		style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
		cell3.setCellStyle(style);
		
//		合并单元格(合并行2和行3)
		CellRangeAddress region = new CellRangeAddress(2, 3, 0, 2);// 起始行号，终止行号， 起始列号，终止列号
		
//		 在sheet里增加合并单元格  
		sheet.addMergedRegion(region);
		
		
		// 4、写入数据
		for (int i = 0; i < userList.size(); i++) {
			row = sheet.createRow(i + headRow);

			row.createCell(0).setCellValue(userList.get(i).getName());
			row.createCell(1).setCellValue(userList.get(i).getSex());
			row.createCell(2).setCellValue(userList.get(i).getAge());
		}


		// 5、将文件储存到指定位置
		try {
			FileOutputStream fout = new FileOutputStream(path+fileName+".xls");
			wb.write(fout);
			fout.close();
		} catch (IOException e) {
			e.printStackTrace();
		}

	}
	
	/**
	 * @param userList
	 * @param head
	 * @param description
	 * @param headRow<br>
	 * <p>导出xlsx</p>
	 */
	public void generateXlsx(List<User> userList, Map<String,String> head,String description,int headRow,String path,String fileName) {
		
		// 1、创建工作簿
//		// excel的文档对象
		XSSFWorkbook wb = new XSSFWorkbook();
		// cell样式
		XSSFCellStyle style = wb.createCellStyle();
		
		XSSFFont font = wb.createFont();
		font.setFontName("仿宋_GB2312");
//		font.setFontName("黑体");
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 粗体显示
		font.setFontHeightInPoints((short) 12);
		
		style.setFont(font);// 选择需要用到的字体格式
		
		// 设置这些样式
		// 水平居中
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		// 垂直居中
		style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		// 背景色
		
		style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
		style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		style.setFillBackgroundColor(HSSFColor.SKY_BLUE.index);
		
		// 设置边框
		style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		style.setBorderRight(HSSFCellStyle.BORDER_THIN);
		style.setBorderTop(HSSFCellStyle.BORDER_THIN);
		
		// 2、创建工作页
		XSSFSheet sheet = wb.createSheet("用户信息");
		for (int i = 0; i < head.size(); i++) {
//			设置列宽
			sheet.setColumnWidth(i, 7000);
		}
		
		
		// 3.创建行
//		表头
		XSSFRow row = sheet.createRow(0);
//		表头说明
		XSSFRow row1 = sheet.createRow(1);
		
		
		int column=0;
		for(String title:head.keySet()) {
			
			XSSFCell cell = row.createCell(column);
			cell.setCellValue(title);
			cell.setCellStyle(style);
			
			XSSFCell cell1 = row1.createCell(column);
			cell1.setCellValue(head.get(title));
			cell1.setCellStyle(style);
			
			column++;
		}
		
//		文档备注行
		XSSFRow row3=sheet.createRow(2);
//		row3.setRowStyle(style);
		XSSFRow row4=sheet.createRow(3);
//		row4.setRowStyle(style);
		XSSFCell cell3=row3.createCell(0);
		cell3.setCellValue(description);
		style.setAlignment(XSSFCellStyle.ALIGN_LEFT);
		cell3.setCellStyle(style);
		
//		合并单元格(合并行2和行3)
		CellRangeAddress region = new CellRangeAddress(2, 3, 0, 2);// 起始行号，终止行号， 起始列号，终止列号
		
//		 在sheet里增加合并单元格  
		sheet.addMergedRegion(region);
		
		
		// 4、写入数据
		for (int i = 0; i < userList.size(); i++) {
			row = sheet.createRow(i + headRow);
			
			row.createCell(0).setCellValue(userList.get(i).getName());
			row.createCell(1).setCellValue(userList.get(i).getSex());
			row.createCell(2).setCellValue(userList.get(i).getAge());
		}
		
		
		// 5、将文件储存到指定位置
		try {
			FileOutputStream fout = new FileOutputStream(path+fileName+".xlsx");
			wb.write(fout);
			fout.close();
		} catch (IOException e) {
			e.printStackTrace();
		}
		
	}

}
